package no.sogl.importer;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;

import no.sogl.sportsadmin.Group;
import no.sogl.sportsadmin.Member;
import no.sogl.sportsadmin.Team;
import no.sogl.util.ExcelHelper;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.springframework.util.StringUtils;

public class SportsAdminToEmitConverter {
    private static final Log log = LogFactory.getLog(SportsAdminToEmitConverter.class);
    private Map<String, List<Team>> teams = new HashMap<String, List<Team>>();
    private Map<String, Map<String, Integer>> teamNos = new HashMap<String, Map<String, Integer>>();
    private AtomicInteger unknownTeamCounter = new AtomicInteger(0);
    private static List<Group> groups = new ArrayList<Group>();

    static {
        groups.add(new Group().setName("G8").setFreeNos(10));
        groups.add(new Group().setName("J8").setFreeNos(4));
        groups.add(new Group().setName("G910").setFreeNos(10));
        groups.add(new Group().setName("J910").setFreeNos(10));
        groups.add(new Group().setName("G1112").setFreeNos(10));
        groups.add(new Group().setName("J1112").setFreeNos(10));
        groups.add(new Group().setName("G1314").setFreeNos(10));
        groups.add(new Group().setName("J1314").setFreeNos(8));
        groups.add(new Group().setName("G1516").setFreeNos(2));
        groups.add(new Group().setName("J1516").setFreeNos(2));
    }

    public static void main(String[] args) throws Exception {
        log.info("Starting converter");
        SportsAdminToEmitConverter converter = new SportsAdminToEmitConverter();
        converter.importSportsAdmin(new File("/Users/espenwest/Dropbox/sogl/Renn/Bråtestafetten2012/sportsadmin.xls"));
        converter.assignAndPrintGroups();
        converter.exportEmit();
    }

    public void importSportsAdmin(File file) throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
        Sheet sheet = wb.getSheetAt(0);
        int rowNo = 0;
        for (Row row : sheet) {
            try {
                if (rowNo < 3) continue;
                parseRow(row);
            } catch (Exception e) {
                log.warn("Error parsing member", e);
            } finally {
                rowNo++;
            }
        }
    }

    private void assignAndPrintGroups() {
        int currentStartNo = 1;
        for (Group g : groups) {
            g.setFromNo(currentStartNo).setToNo(currentStartNo + teams.get(g.getName()).size() - 1 + g.getFreeNos());
            currentStartNo = g.getToNo() + 1;

            List<Team> firstSeeds = new ArrayList<Team>();
            List<Team> secondSeeds = new ArrayList<Team>();
            List<Team> thirdSeeds = new ArrayList<Team>();
            for (Team team : teams.get(g.getName())) {
                if (team.getGroup().endsWith(" 1") || team.getGroup().matches("(?is).*? 1\\..*")) firstSeeds.add(team);
                else if (team.getGroup().endsWith(" 2") || team.getGroup().matches("(?is).*? 2\\..*")) secondSeeds.add(team);
                else thirdSeeds.add(team);

            }
            Collections.shuffle(firstSeeds);
            Collections.shuffle(secondSeeds);
            Collections.shuffle(thirdSeeds);

            int startNo = g.getFromNo();
            for (Team team : firstSeeds)
                team.setStartNo(startNo++).setGroupNo(getAndIncrementTeamNo(g.getName(), team.getMembers().get(0).getClub()));

            for (Team team : secondSeeds)
                team.setStartNo(startNo++).setGroupNo(getAndIncrementTeamNo(g.getName(), team.getMembers().get(0).getClub()));

            for (Team team : thirdSeeds)
                team.setStartNo(startNo++).setGroupNo(getAndIncrementTeamNo(g.getName(), team.getMembers().get(0).getClub()));

            System.out.format("%6.6s: %d - %d->%d (%d, %d, %d)%n", g.getName(), teams.get(g.getName()).size(), g.getFromNo(),
                g.getToNo(), firstSeeds.size(), secondSeeds.size(), thirdSeeds.size());

        }
    }

    private void exportEmit() throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        Map<String, CellStyle> styles = ExcelHelper.createStyles(wb);
        int rowNo = 1;
        HSSFSheet sheet = wb.createSheet("Ark 1");
        createHeaders(styles, sheet);

        for (Group g : groups) {
            for (Team team : teams.get(g.getName())) {
                for (Member member : team.getMembers())
                    addRow(g.getName(), team, member, sheet, rowNo++, styles);
            }
        }

        wb.write(new FileOutputStream(new File("/Users/espenwest/Dropbox/sogl/Renn/Bråtestafetten2012/emit_import_"
                + DateTimeFormat.forPattern("yyyyMMdd").print(new DateTime()) + ".xls")));
    }

    private void parseRow(Row row) {
        String strGroup = ExcelHelper.getCellString(row.getCell(0)).replaceAll("-", "").replaceAll(" ", "");
        String strTeam = ExcelHelper.getCellString(row.getCell(18));
        if (!StringUtils.hasText(strTeam)) strTeam = "Ukjent " + unknownTeamCounter.incrementAndGet();
        Team team = getOrAddTeam(strGroup, strTeam);
        Member member = new Member().setBirthDate(ExcelHelper.getCellDate(row.getCell(7), "dd.MM.yyyy"))
            .setFirstName(ExcelHelper.getCellString(row.getCell(5)))
            .setLastName(ExcelHelper.getCellString(row.getCell(4)))
            .setGender(ExcelHelper.getCellString(row.getCell(6)))
            .setRelayNo(ExcelHelper.getCellInteger(row.getCell(19)))
            .setClub(ExcelHelper.getCellString(row.getCell(9)));
        team.addMember(member);
        System.out.println(member);
    }

    private Team getOrAddTeam(String group, String team) {
        List<Team> groupTeams = teams.get(group);
        if (groupTeams == null) {
            groupTeams = new ArrayList<Team>();
            teams.put(group, groupTeams);
        }
        Team theTeam = null;
        for (Team t : groupTeams) {
            if (t.getGroup().equals(team)) {
                theTeam = t;
                break;
            }
        }
        if (theTeam == null) {
            theTeam = new Team().setGroup(team);
            groupTeams.add(theTeam);
        }
        return theTeam;
    }

    private int getAndIncrementTeamNo(String group, String team) {
        Map<String, Integer> nosForGroup = teamNos.get(group);
        if (nosForGroup == null) {
            nosForGroup = new HashMap<String, Integer>();
            teamNos.put(group, nosForGroup);
        }
        Integer currentNo = nosForGroup.get(team);
        if (currentNo == null) currentNo = 0;
        currentNo++;
        nosForGroup.put(team, currentNo);
        return currentNo;
    }

    private void createHeaders(Map<String, CellStyle> styles, HSSFSheet sheet) {
        Row row = sheet.createRow(0);
        row.setHeightInPoints(16);
        ExcelHelper.createCell(row, 0, "Fornavn", styles.get("header"));
        sheet.setColumnWidth(0, 15 * 256);
        ExcelHelper.createCell(row, 1, "Etternavn", styles.get("header"));
        sheet.setColumnWidth(1, 15 * 256);
        ExcelHelper.createCell(row, 2, "Klasse", styles.get("header"));
        sheet.setColumnWidth(2, 15 * 256);
        ExcelHelper.createCell(row, 3, "Klubb navn", styles.get("header"));
        sheet.setColumnWidth(3, 15 * 256);
        ExcelHelper.createCell(row, 4, "Løype/distanse", styles.get("header"));
        sheet.setColumnWidth(4, 15 * 256);
        ExcelHelper.createCell(row, 5, "Kjønn", styles.get("header"));
        sheet.setColumnWidth(5, 8 * 256);
        ExcelHelper.createCell(row, 6, "Startno", styles.get("header"));
        sheet.setColumnWidth(6, 11 * 256);
        ExcelHelper.createCell(row, 7, "Brikkenr", styles.get("header"));
        sheet.setColumnWidth(7, 11 * 256);
        ExcelHelper.createCell(row, 8, "Starttid (HH:MM:SS)", styles.get("header"));
        sheet.setColumnWidth(8, 11 * 256);
        ExcelHelper.createCell(row, 9, "Kid", styles.get("header"));
        sheet.setColumnWidth(9, 11 * 256);
        ExcelHelper.createCell(row, 10, "Lagnr", styles.get("header"));
        sheet.setColumnWidth(10, 20 * 256);
        ExcelHelper.createCell(row, 11, "Etappenr", styles.get("header"));
        sheet.setColumnWidth(11, 12 * 256);
        ExcelHelper.createCell(row, 12, "Nasj. kode", styles.get("header"));
        sheet.setColumnWidth(12, 30 * 256);
    }

    private void addRow(String group, Team team, Member member, HSSFSheet sheet, int rowNo, Map<String, CellStyle> styles) {
        Row row = sheet.createRow(rowNo);
        ExcelHelper.createCell(row, 0, member.getFirstName(), styles.get("left"));
        ExcelHelper.createCell(row, 1, member.getLastName(), styles.get("left"));
        ExcelHelper.createCell(row, 2, group, styles.get("left"));
        ExcelHelper.createCell(row, 3, member.getClub(), styles.get("left"));
        ExcelHelper.createCell(row, 5, member.getGender(), styles.get("left"));
        ExcelHelper.createCell(row, 6, team.getStartNo(), styles.get("right"));
        ExcelHelper.createCell(row, 10, team.getGroupNo(), styles.get("right"));
        ExcelHelper.createCell(row, 11, member.getRelayNo(), styles.get("right"));

    }
}
